{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Manpower Planning\n",
    "\n",
    "## Objective and Prerequisites\n",
    "\n",
    "Staffing problems – which require difficult decisions about the recruitment, training, layoffs, and scheduling of workers – are common across a broad range of manufacturing and service industries. In this example, you’ll learn how to model and solve a complex staffing problem by creating an optimal multi-period operation plan that minimizes the total number of layoffs and costs.\n",
    "\n",
    "More information on this type of model can be found in example #5 of the fifth edition of Model Building in Mathematical Programming by H. Paul Williams on pages 256 – 257 and 303 – 304.\n",
    "\n",
    "This modeling example is at the advanced level, where we assume that you know Python and the Gurobi Python API and that you have advanced knowledge of building mathematical optimization models. Typically, the objective function and/or constraints of these examples are complex or require advanced features of the Gurobi Python API.\n",
    "\n",
    "**Download the Repository** <br />\n",
    "You can download the repository containing this and other examples by clicking [here](https://github.com/Gurobi/modeling-examples/archive/master.zip). \n",
    "\n",
    "---\n",
    "## Problem Description\n",
    "\n",
    "A company is changing how it runs its business, and therefore its staffing needs are expected to change.\n",
    "\n",
    "Through the purchase of new machinery, it is expected that there will be less need for unskilled labor and more need for skilled and semi-skilled labor. In addition, a lower sales forecast ⁠— driven by an economic slowdown that is predicted to happen in the next year ⁠— is expected to further reduce labor needs across all categories.\n",
    "\n",
    "The forecast for labor needs over the next three years is as follows:\n",
    "\n",
    "| <i></i> | Unskilled | Semi-skilled | Skilled |\n",
    "| --- | --- | --- | --- |\n",
    "| Current Strength | 2000 | 1500 | 1000 |\n",
    "| Year 1 | 1000 | 1400 | 1000 |\n",
    "| Year 2 | 500 | 2000 | 1500 |\n",
    "| Year 3 | 0 | 2500 | 2000 |\n",
    "\n",
    "The company needs to determine the following for each of the next three years:\n",
    "\n",
    "- Recruitment\n",
    "- Retraining\n",
    "- Layoffs (redundancy)\n",
    "- Part-time vs. full-time employees\n",
    "\n",
    "It is important to note that labor is subject to a certain level of natural attrition each year. The rate of attrition is relatively high in the first year after a new employee is hired and relatively low in subsequent years. The expected attrition rates are as follows:\n",
    "\n",
    "| <i></i> | Unskilled (%)| Semi-skilled (%) | Skilled (%) |\n",
    "| --- | --- | --- | --- |\n",
    "| $< 1$ year of service | 25 | 20 | 10 |\n",
    "| $\\geq 1$ year of service | 10 | 5 | 5 |\n",
    "\n",
    "All of the current workers have been with the company for at least one year.\n",
    "\n",
    "### Recruitment\n",
    "\n",
    "Each year, it is possible to hire a limited number of employees in each classification from outside the company as follows:\n",
    "\n",
    "| Unskilled | Semi-skilled | Skilled |\n",
    "| --- | --- | --- |\n",
    "| 500 | 800 | 500 |\n",
    "\n",
    "### Retraining\n",
    "\n",
    "Each year, it is possible to train up to 200 unskilled workers to make them into semi-skilled workers. This training costs the company $\\$400$ per worker.\n",
    "\n",
    "In addition, it is possible train semi-skilled workers to make them into skilled workers. However, this number can not exceed 25% of the current skilled labor force and this training costs $\\$500$ per worker.\n",
    "\n",
    "Lastly, downgrading workers to a lower skill level can be done. However, 50% of the downgraded workers will leave the company, increasing the natural attrition rate described above.\n",
    "\n",
    "### Layoffs\n",
    "\n",
    "Each laid-off worker is entitled to a separation payment at the rate of $\\$200$ per unskilled worker and $\\$500$ per semi-skilled or skilled worker.\n",
    "\n",
    "### Excess Employees\n",
    "\n",
    "It is possible to have workers in excess of the actual number needed, up to 150 workers in total in any given year, but this will result in the following additional cost per excess employee per year.\n",
    "\n",
    "| Unskilled | Semi-skilled | Skilled |\n",
    "| --- | --- | --- |\n",
    "| $\\$1500$ | $\\$2000$ | $\\$3000$ |\n",
    "\n",
    "### Part-time Workers\n",
    "\n",
    "Up to 50 employees of each skill level can be assigned to part-time work. The cost of doing so (per employee, per year) is as follows:\n",
    "\n",
    "| Unskilled | Semi-skilled | Skilled |\n",
    "| --- | --- | --- |\n",
    "| $\\$500$ | $\\$400$ | $\\$400$ |\n",
    "\n",
    "**Note:** A part-time employee is half as productive as a full-time employee.\n",
    "\n",
    "If the company’s objective is to minimize layoffs, what plan should they adopt in order to do this?\n",
    "\n",
    "If their objective is to minimize costs, how much could they further reduce costs?\n",
    "\n",
    "How can they determine the annual savings possible across each job?\n",
    "\n",
    "---\n",
    "## Model Formulation\n",
    "\n",
    "### Sets and Indices\n",
    "\n",
    "$t \\in \\text{Years}=\\{1,2,3\\}$: Set of years.\n",
    "\n",
    "$s \\in \\text{Skills}=\\{s_1: \\text{unskilled},s_2: \\text{semi_skilled},s_3: \\text{skilled}\\}$: Set of skills.\n",
    "\n",
    "### Parameters\n",
    "\n",
    "$\\text{rookie_attrition} \\in [0,1] \\subset \\mathbb{R}^+$: Percentage of workers who leave within the first year of service.\n",
    "\n",
    "$\\text{veteran_attrition} \\in [0,1] \\subset \\mathbb{R}^+$: Percentage of workers who leave after the first year of service.\n",
    "\n",
    "$\\text{demoted_attrition} \\in [0,1] \\subset \\mathbb{R}^+$: Percentage of workers who leave the company after a demotion.\n",
    "\n",
    "$\\text{parttime_cap} \\in [0,1] \\subset \\mathbb{R}^+$: Productivity of part-time workers with respect to full-time workers.\n",
    "\n",
    "$\\text{max_train_unskilled} \\in \\mathbb{N}$: Maximum number of unskilled workers that can be trained on any given year.\n",
    "\n",
    "$\\text{max_train_semiskilled} \\in [0,1] \\subset \\mathbb{R}^+$: Maximum proportion of semi-skilled workers (w.r.t. skilled ones) that can be trained on any given year.\n",
    "\n",
    "$\\text{max_parttime} \\in \\mathbb{N}$: Maximum number of part-time workers of each skill at any given year.\n",
    "\n",
    "$\\text{max_overmanning} \\in \\mathbb{N}$: Maximum number of overmanned workers at any given year.\n",
    "\n",
    "$\\text{max_hiring}_s \\in \\mathbb{N}$: Maximum number of workers of skill $s$ that can be hired any given year.\n",
    "\n",
    "$\\text{training_cost}_s \\in \\mathbb{R}^+$: Cost for training a worker of skill $s$ to the next level.\n",
    "\n",
    "$\\text{layoff_cost}_s \\in \\mathbb{R}^+$: Cost for laying off a worker of skill $s$.\n",
    "\n",
    "$\\text{parttime_cost}_s \\in \\mathbb{R}^+$: Cost for assigning a worker of skill $s$ to part-time work.\n",
    "\n",
    "$\\text{overmanning_cost}_s \\in \\mathbb{R}^+$: Yearly cost for having excess manpower of skill $s$.\n",
    "\n",
    "$\\text{curr_workforce}_s \\in \\mathbb{N}$: Current manpower of skill $s$ at the beginning of the planning horizon.\n",
    "\n",
    "$\\text{demand}_{t,s} \\in \\mathbb{N}$: Required manpower of skill $s$ in year $t$.\n",
    "\n",
    "\n",
    "### Decision Variables\n",
    "\n",
    "$\\text{hire}_{t,s} \\in [0,\\text{max_hiring}_s] \\subset \\mathbb{R}^+$: Number of workers of skill $s$ to hire in year $t$.\n",
    "\n",
    "$\\text{part_time}_{t,s} \\in [0,\\text{max_parttime}] \\subset \\mathbb{R}^+$: Number of part-time workers of skill $s$ working in year $t$.\n",
    "\n",
    "$\\text{workforce}_{t,s} \\in \\mathbb{R}^+$: Number of workers of skill $s$ that are available in year $t$.\n",
    "\n",
    "$\\text{layoff}_{t,s} \\in \\mathbb{R}^+$: Number of workers of skill $s$ that are laid off in year $t$.\n",
    "\n",
    "$\\text{excess}_{t,s} \\in \\mathbb{R}^+$: Number of workers of skill $s$ that are overmanned in year $t$.\n",
    "\n",
    "$\\text{train}_{t,s,s'} \\in \\mathbb{R}^+$: Number of workers of skill $s$ to retrain to skill $s'$ in year $t$.\n",
    "\n",
    "### Objective Function\n",
    "\n",
    "- **Layoffs:** Minimize the total layoffs during the planning horizon.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{Minimize} \\quad Z = \\sum_{t \\in \\text{Years}}\\sum_{s \\in \\text{Skills}}{\\text{layoff}_{t,s}}\n",
    "\\end{equation}\n",
    "\n",
    "- **Cost:** Minimize the total cost (in USD) incurred by training, overmanning, part-time workers, and layoffs in the planning horizon.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{Minimize} \\quad W = \\sum_{t \\in \\text{Years}}{\\{\\text{training_cost}_{s_1}*\\text{train}_{t,s1,s2} + \\text{training_cost}_{s_2}*\\text{train}_{t,s2,s3}\\}}\n",
    "\\end{equation}\n",
    "\n",
    "\\begin{equation}\n",
    "+ \\sum_{t \\in \\text{Years}}\\sum_{s \\in \\text{Skills}}{\\{\\text{parttime_cost}*\\text{part_time}_{t,s} + \\text{layoff_cost}_s*\\text{layoff}_{t,s} + \\text{overmanning_cost}_s*\\text{excess}_{t,s}\\}}\n",
    "\\end{equation}\n",
    "\n",
    "### Constraints\n",
    "\n",
    "- **Initial Balance:** Workforce $s$ available in year $t=1$ is equal to the workforce of the previous year, recent hires, promoted and demoted workers (after accounting for attrition), minus layoffs and transferred workers.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{workforce}_{1,s} = (1-\\text{veteran_attrition}_s)*\\text{curr_workforce} + (1-\\text{rookie_attrition}_s)*\\text{hire}_{1,s} \n",
    "\\end{equation}\n",
    "\n",
    "\\begin{equation}\n",
    "+ \\sum_{s' \\in \\text{Skills} | s' < s}{\\{(1-\\text{veteran_attrition})*\\text{train}_{1,s',s} - \\text{train}_{1,s,s'}\\}} \n",
    "\\end{equation}\n",
    "\n",
    "\\begin{equation}\n",
    "+ \\sum_{s' \\in \\text{Skills} | s' > s}{\\{(1-\\text{demoted_attrition})*\\text{train}_{1,s',s} - \\text{train}_{1,s,s'}\\}} - \\text{layoff}_{1,s} \\qquad \\forall s \\in \\text{Skills}\n",
    "\\end{equation}\n",
    "\n",
    "\n",
    "- **Balance:** Workforce $s$ available in year $t > 1$ is equal to the workforce of the previous year, recent hires, promoted and demoted workers (after accounting for attrition), minus layoffs and transferred workers.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{workforce}_{t,s} = (1-\\text{veteran_attrition}_s)*\\text{workforce}_{t-1,s} + (1-\\text{rookie_attrition}_s)*\\text{hire}_{t,s} \n",
    "\\end{equation}\n",
    "\n",
    "\\begin{equation}\n",
    "+ \\sum_{s' \\in \\text{Skills} | s' < s}{\\{(1-\\text{veteran_attrition})*\\text{train}_{t,s',s} - \\text{train}_{t,s,s'}\\}}\n",
    "\\end{equation}\n",
    "\n",
    "\\begin{equation}\n",
    "+ \\sum_{s' \\in \\text{Skills} | s' > s}{\\{(1-\\text{demotion_attrition})*\\text{train}_{t,s',s} - \\text{train}_{t,s,s'}\\}} - \\text{layoff}_{t,s} \\quad \\forall (t > 1,s) \\in \\text{Years} \\times \\text{Skills}\n",
    "\\end{equation}\n",
    "\n",
    "- **Unskilled Training:** Unskilled workers trained in year $t$ cannot exceed the maximum allowance. Unskilled workers cannot be immediately transformed into skilled workers.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{train}_{t,s_1,s_2} \\leq 200 \\quad \\forall t \\in \\text{Years}\n",
    "\\end{equation}\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{train}_{t,s_1,s_3} = 0 \\quad \\forall t \\in \\text{Years}\n",
    "\\end{equation}\n",
    "\n",
    "- **Semi-skilled Training:** Semi-skilled workers trained in year $t$ cannot exceed the maximum allowance.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{train}_{t,s_2,s_3} \\leq 0.25*\\text{available}_{t,s_3} \\quad \\forall t \\in \\text{Years}\n",
    "\\end{equation}\n",
    "\n",
    "- **Overmanning:** Excess workers in year $t$ cannot exceed the maximum allowance.\n",
    "\n",
    "\\begin{equation}\n",
    "\\sum_{s \\in \\text{Skills}}{\\text{excess}_{t,s}} \\leq \\text{max_overmanning} \\quad \\forall t \\in \\text{Years}\n",
    "\\end{equation}\n",
    "\n",
    "- **Demand:** Workforce $s$ available in year $t$ equals the required number of workers plus the excess workers and the part-time workers.\n",
    "\n",
    "\\begin{equation}\n",
    "\\text{available}_{t,s} = \\text{demand}_{t,s} + \\text{excess}_{t,s} + \\text{parttime_cap}*\\text{part_time}_{t,s} \\quad \\forall (t,s) \\in \\text{Years} \\times \\text{Skills}\n",
    "\\end{equation}\n",
    "\n",
    "---\n",
    "## Python Implementation\n",
    "\n",
    "We import the Gurobi Python Module and other Python libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install gurobipy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import gurobipy as gp\n",
    "from gurobipy import GRB\n",
    "\n",
    "# tested with Python 3.11 & Gurobi 11.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Input Data\n",
    "We define all the input data of the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parameters\n",
    "\n",
    "years = [1, 2, 3]\n",
    "skills = ['s1', 's2', 's3']\n",
    "\n",
    "curr_workforce = {'s1': 2000, 's2': 1500, 's3': 1000}\n",
    "demand = {\n",
    "    (1, 's1'): 1000,\n",
    "    (1, 's2'): 1400,\n",
    "    (1, 's3'): 1000,\n",
    "    (2, 's1'): 500,\n",
    "    (2, 's2'): 2000,\n",
    "    (2, 's3'): 1500,\n",
    "    (3, 's1'): 0,\n",
    "    (3, 's2'): 2500,\n",
    "    (3, 's3'): 2000\n",
    "}\n",
    "rookie_attrition = {'s1': 0.25, 's2': 0.20, 's3': 0.10}\n",
    "veteran_attrition = {'s1': 0.10, 's2': 0.05, 's3': 0.05}\n",
    "demoted_attrition = 0.50\n",
    "max_hiring = {\n",
    "    (1, 's1'): 500,\n",
    "    (1, 's2'): 800,\n",
    "    (1, 's3'): 500,\n",
    "    (2, 's1'): 500,\n",
    "    (2, 's2'): 800,\n",
    "    (2, 's3'): 500,\n",
    "    (3, 's1'): 500,\n",
    "    (3, 's2'): 800,\n",
    "    (3, 's3'): 500\n",
    "}\n",
    "max_overmanning = 150\n",
    "max_parttime = 50\n",
    "parttime_cap = 0.50\n",
    "max_train_unskilled = 200\n",
    "max_train_semiskilled = 0.25\n",
    "\n",
    "training_cost = {'s1': 400, 's2': 500}\n",
    "layoff_cost = {'s1': 200, 's2': 500, 's3': 500}\n",
    "parttime_cost = {'s1': 500, 's2': 400, 's3': 400}\n",
    "overmanning_cost = {'s1': 1500, 's2': 2000, 's3': 3000}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Model Deployment\n",
    "We create a model and the variables. For each of the three skill levels and for each year, we will create variables for the number of workers that get recruited, transferred into part-time work, are available as workers, are redundant, or are overmanned. For each pair of skill levels and each year, we have a variable for the amount of workers that get retrained to a higher/lower skill level. The number of people who are part-time and can be recruited is limited."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using license file c:\\gurobi\\gurobi.lic\n"
     ]
    }
   ],
   "source": [
    "manpower = gp.Model('Manpower planning')\n",
    "\n",
    "hire = manpower.addVars(years, skills, ub=max_hiring, name=\"Hire\")\n",
    "part_time = manpower.addVars(years, skills, ub=max_parttime,\n",
    "                          name=\"Part_time\")\n",
    "workforce = manpower.addVars(years, skills, name=\"Available\")\n",
    "layoff = manpower.addVars(years, skills, name=\"Layoff\")\n",
    "excess = manpower.addVars(years, skills, name=\"Overmanned\")\n",
    "train = manpower.addVars(years, skills, skills, name=\"Train\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we insert the constraints. The balance constraints ensure that per skill level and per year the workers who are currently required (LaborForce) and the people who get laid off, and the people who get retrained to the current level, minus the people who get retrained from the current level to a different skill, equals the LaborForce of the last year (or the CurrentStrength in the first year) plus the recruited people. A certain amount of people leave the company each year, so this is also considered to be a factor. This constraint describes the change in the total amount of employed workers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1.1 & 1.2 Balance\n",
    "\n",
    "Balance = manpower.addConstrs(\n",
    "    (workforce[year, level] == (1-veteran_attrition[level])*(curr_workforce[level] if year == 1 else workforce[year-1, level])\n",
    "    + (1-rookie_attrition[level])*hire[year, level] + gp.quicksum((1- veteran_attrition[level])* train[year, level2, level]\n",
    "                                                        -train[year, level, level2] for level2 in skills if level2 < level)\n",
    "    + gp.quicksum((1- demoted_attrition)* train[year, level2, level] -train[year, level, level2] for level2 in skills if level2 > level)\n",
    "    - layoff[year, level] for year in years for level in skills), \"Balance\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Unskilled training constraints force that per year only 200 workers can be retrained from Unskilled to Semi-skilled due to capacity limitations. Also, no one can be trained in one year from Unskilled to Skilled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2.1 & 2.2  Unskilled training\n",
    "UnskilledTrain1 = manpower.addConstrs((train[year, 's1', 's2'] <= max_train_unskilled for year in years), \"Unskilled_training1\")\n",
    "UnskilledTrain2 = manpower.addConstrs((train[year, 's1', 's3'] == 0 for year in years), \"Unskilled_training2\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Semi-skilled training states that the retraining of Semi-skilled workers to skilled workers is limited to no more than one quarter of the skilled labor force at this time. This is due to capacity limitations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#3. Semi-skilled training\n",
    "\n",
    "SemiskilledTrain = manpower.addConstrs((train[year,'s2', 's3'] <= max_train_semiskilled * workforce[year,'s3'] for year in years), \"Semiskilled_training\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The overmanning constraints ensure that the total overmanning over all skill levels in one year is no more than 150."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#4. Overmanning\n",
    "Overmanning = manpower.addConstrs((excess.sum(year, '*') <= max_overmanning for year in years), \"Overmanning\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The demand constraints ensure that the number of workers of each level and year equals the required number of workers plus the Overmanned workers and the number of workers who are working part-time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#5. Demand\n",
    "Demand = manpower.addConstrs((workforce[year, level] ==\n",
    "     demand[year,level] + excess[year, level] + parttime_cap * part_time[year, level]\n",
    "                     for year in years for level in skills), \"Requirements\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first objective is to minimize the total number of laid off workers. This can be stated as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#0.1 Objective Function: Minimize layoffs\n",
    "obj1 = layoff.sum()\n",
    "manpower.setObjective(obj1, GRB.MINIMIZE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The second alternative objective is to minimize the total cost of all employed workers and costs for retraining:\n",
    "\n",
    "```\n",
    "obj2 = quicksum((training_cost[level]*train[year, level, skills[skills.index(level)+1]] if level < 's3' else 0)\n",
    "                + layoff_cost[level]*layoff[year, level]\n",
    "                + parttime_cost[level]*part_time[year, level]\n",
    "                + overmanning_cost[level] * excess[year, level] for year in years for level in skills)\n",
    "```\n",
    "\n",
    "Next we start the optimization with the objective function of minimizing layoffs, and Gurobi finds the optimal solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Gurobi Optimizer version 9.1.0 build v9.1.0rc0 (win64)\n",
      "Thread count: 4 physical cores, 8 logical processors, using up to 8 threads\n",
      "Optimize a model with 30 rows, 72 columns and 117 nonzeros\n",
      "Model fingerprint: 0x03e41c91\n",
      "Coefficient statistics:\n",
      "  Matrix range     [3e-01, 1e+00]\n",
      "  Objective range  [1e+00, 1e+00]\n",
      "  Bounds range     [5e+01, 8e+02]\n",
      "  RHS range        [2e+02, 3e+03]\n",
      "Presolve removed 18 rows and 44 columns\n",
      "Presolve time: 0.01s\n",
      "Presolved: 12 rows, 28 columns, 56 nonzeros\n",
      "\n",
      "Iteration    Objective       Primal Inf.    Dual Inf.      Time\n",
      "       0    8.4000000e+02   6.484375e+01   0.000000e+00      0s\n",
      "       8    8.4179688e+02   0.000000e+00   0.000000e+00      0s\n",
      "\n",
      "Solved in 8 iterations and 0.01 seconds\n",
      "Optimal objective  8.417968750e+02\n"
     ]
    }
   ],
   "source": [
    "manpower.optimize()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis\n",
    "\n",
    "The minimum number of layoffs is 841.80. The optimal policies to achieve this minimum number of layoffs are given below.\n",
    "\n",
    "\n",
    "### Hiring Plan\n",
    "This plan determines the number of new workers to hire at each year of the planning horizon (rows) and each skill level (columns). For example, at year 2 we are going to hire 649.3 Semi-skilled workers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>649.3</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>677.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    s1     s2     s3\n",
       "1  0.0    0.0    0.0\n",
       "2  0.0  649.3  500.0\n",
       "3  0.0  677.0  500.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = years.copy()\n",
    "columns = skills.copy()\n",
    "hire_plan = pd.DataFrame(columns=columns, index=rows, data=0.0)\n",
    "\n",
    "for year, level in hire.keys():\n",
    "    if (abs(hire[year, level].x) > 1e-6):\n",
    "        hire_plan.loc[year, level] = np.round(hire[year, level].x, 1)\n",
    "hire_plan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Training and Demotions Plan\n",
    "This plan defines the number of workers to promote by training (or demote) at each year of the planning horizon. For example, in year 1 we are going to demote 168.4 skilled (s3) workers to the level of semi-skilled (s2)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1 to s2</th>\n",
       "      <th>s1 to s3</th>\n",
       "      <th>s2 to s1</th>\n",
       "      <th>s2 to s3</th>\n",
       "      <th>s3 to s1</th>\n",
       "      <th>s3 to s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>256.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>168.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>131.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   s1 to s2  s1 to s3  s2 to s1  s2 to s3  s3 to s1  s3 to s2\n",
       "1     200.0       0.0       0.0     256.2       0.0     168.4\n",
       "2     200.0       0.0       0.0      80.3       0.0       0.0\n",
       "3     200.0       0.0       0.0     131.6       0.0       0.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = years.copy()\n",
    "columns = ['{0} to {1}'.format(level1, level2) for level1 in skills for level2 in skills if level1 != level2]\n",
    "train_plan = pd.DataFrame(columns=columns, index=rows, data=0.0)\n",
    "\n",
    "for year, level1, level2 in train.keys():\n",
    "    col = '{0} to {1}'.format(level1, level2)\n",
    "    if (abs(train[year, level1, level2].x) > 1e-6):\n",
    "        train_plan.loc[year, col] = np.round(train[year, level1, level2].x, 1)\n",
    "train_plan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Layoffs Plan\n",
    "\n",
    "This plan determines the number of workers to  layoff of each skill level at each year of the planning horizon. For example, we are going to layoff 232.5 Unskilled workers in year 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>443.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>166.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>232.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      s1   s2   s3\n",
       "1  443.0  0.0  0.0\n",
       "2  166.3  0.0  0.0\n",
       "3  232.5  0.0  0.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = years.copy()\n",
    "columns = skills.copy()\n",
    "layoff_plan = pd.DataFrame(columns=columns, index=rows, data=0.0)\n",
    "\n",
    "for year, level in layoff.keys():\n",
    "    if (abs(layoff[year, level].x) > 1e-6):\n",
    "        layoff_plan.loc[year, level] = np.round(layoff[year, level].x, 1)\n",
    "layoff_plan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part-time Plan\n",
    "\n",
    "This plan defines the number of part-time workers of each skill level working at each year of the planning horizon. For example, in year 1, we have 50 part-time skilled workers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     s1    s2    s3\n",
       "1  50.0  50.0  50.0\n",
       "2  50.0   0.0   0.0\n",
       "3  50.0   0.0   0.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = years.copy()\n",
    "columns = skills.copy()\n",
    "parttime_plan = pd.DataFrame(columns=columns, index=rows, data=0.0)\n",
    "\n",
    "for year, level in part_time.keys():\n",
    "    if (abs(part_time[year, level].x) > 1e-6):\n",
    "        parttime_plan.loc[year, level] = np.round(part_time[year, level].x, 1)\n",
    "parttime_plan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overmanning Plan\n",
    "\n",
    "This plan determines the number of excess workers of each skill level working at each year of the planning horizon. For example, we have 150 Unskilled excess workers in year 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>132.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      s1    s2   s3\n",
       "1  132.0  18.0  0.0\n",
       "2  150.0   0.0  0.0\n",
       "3  150.0   0.0  0.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = years.copy()\n",
    "columns = skills.copy()\n",
    "excess_plan = pd.DataFrame(columns=columns, index=rows, data=0.0)\n",
    "\n",
    "for year, level in excess.keys():\n",
    "    if (abs(excess[year, level].x) > 1e-6):\n",
    "        excess_plan.loc[year, level] = np.round(excess[year, level].x, 1)\n",
    "excess_plan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By minimizing the cost instead, we could implement policies that would cost $\\$498,677.29$ over the three-year period and result in 1,423.7 layoffs. Alternative optimal solutions could be considered to reduce layoffs without increasing cost. If we minimize costs instead of layoffs, we can save $\\$942,712.51$ at the expense of 581.9 additional layoffs. Thus, the cost of saving each job, when minimizing layoffs, could be regarded as $\\$1,620.06$.\n",
    "\n",
    "**Note:** If you want to write your solution to a file, rather than print it to the terminal, you can use the model.write() command. An example implementation is:\n",
    "\n",
    "`manpower.write(\"manpower-planning-output.sol\")`\n",
    "\n",
    "---\n",
    "## References\n",
    "\n",
    "H. Paul Williams, Model Building in Mathematical Programming, fifth edition.\n",
    "\n",
    "Copyright &copy; 2020 Gurobi Optimization, LLC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
